package com.teamwork;

import java.sql.*;

public class AutoGradeExam {

    public static void Garding() {
        Connection conn = null;
        PreparedStatement stmt = null;
        ResultSet rs = null;

        try {
            conn = DBUtil.getConnection();  // 获取数据库连接
            // 设置存储过程参数
            int exam_id = 1;  // 假设考试ID为1
            int question_score = 10;  // 假设每题分数为10分
            // 使用PreparedStatement执行存储过程的逻辑
            // 首先查询答案表中的数据
            String query = "SELECT SId, SCId, SAn, CHId FROM answer WHERE EId = ?";
            stmt = conn.prepareStatement(query);
            stmt.setInt(1, exam_id);
            rs = stmt.executeQuery();
            int total_score = 0;

            // 处理结果集
            while (rs.next()) {
                int student_id = rs.getInt("SId");
                int sc_id = rs.getInt("SCId");
                String student_answer = rs.getString("SAn");
                int question_id = rs.getInt("CHId");

                // 查询正确答案
                String query_correct_answer = "SELECT CHKey FROM examchoice WHERE EId = ? AND CHId = ?";
                PreparedStatement stmt_correct_answer = conn.prepareStatement(query_correct_answer);
                stmt_correct_answer.setInt(1, exam_id);
                stmt_correct_answer.setInt(2, question_id);
                ResultSet rs_correct_answer = stmt_correct_answer.executeQuery();

                String correct_answer = null;
                if (rs_correct_answer.next()) {
                    correct_answer = rs_correct_answer.getString("CHKey");
                }

                // 判定答案是否正确
                int score = (student_answer.equals(correct_answer)) ? question_score : 0;

                // 累加学生得分
                total_score += score;

                // 更新学生成绩表
                String update_query = "UPDATE sc SET SScore = ? WHERE SCId = ?";
                PreparedStatement update_stmt = conn.prepareStatement(update_query);
                update_stmt.setInt(1, total_score);
                update_stmt.setInt(2, sc_id); // 使用当前循环的sc_id
                update_stmt.executeUpdate();

                // 关闭结果集和语句
                rs_correct_answer.close();
                stmt_correct_answer.close();
                update_stmt.close();
            }

        } catch (SQLException se) {
            // 处理JDBC错误
            se.printStackTrace();
        } finally {
            // 关闭资源
            try {
                if (rs != null) rs.close();
                if (stmt != null) stmt.close();
                if (conn != null) conn.close();
            } catch (SQLException se) {
                se.printStackTrace();
            }
        }
    }
}
